package net.bingosoft.common.data;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import net.bingosoft.common.BeanHelper;
import net.bingosoft.common.config.Config;
import net.bingosoft.common.data.SQLProvider.DynamicSQL;
import net.bingosoft.common.json.JSONArray;
import net.bingosoft.common.json.JSONConverter;
import net.bingosoft.common.json.JSONObject;
import net.bingosoft.common.struts.BaseActionForm;
import net.bingosoft.common.struts.BaseLazyDynaForm;
import net.bingosoft.common.util.ClassUtil;
import net.bingosoft.common.util.LimitUtil;
import net.bingosoft.common.util.SQLHelper;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.ecside.table.limit.Limit;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;
import org.springframework.jdbc.support.JdbcUtils;

import bingo.persister.Persister;

import com.jenkov.mrpersister.impl.ResultSetProcessorBase;
import com.jenkov.mrpersister.itf.PersistenceException;
import com.jenkov.mrpersister.itf.command.IDaos;

/**
 * 2007-12-26 下午12:41:46
 * Author:yangdongyu(yangdy@bingosoft.net)
 * ReadMe:
 * ChangeLog:
 */
public class PersisterDao implements BeanFactoryAware, BaseDao{

	protected final Log log = LogFactory.getLog(PersisterDao.class);
	private Persister persister;
	private DataSource datasource;
	//创建baseDao的BeanFactory
	private BeanFactory factory ;
	private Map<String,Persister> persisterHolder = new HashMap<String,Persister>();
	private Map<String,BaseDao> daoHolder = new HashMap<String,BaseDao>();
	private static final String defalutSequenceName = Config.getString("DEFALUT_SEQUENCE_NAME","DEFALUT_SEQUENCE");
	/**
	 * 用来生成序列的序列名称
	 */
	private String sequenceName;
//	
//	/**
//	 * 主键生成类
//	 */
//	private IdGenerator generator;
//	
//	private String generatorName;
//
//	public String getGeneratorName() {
//		return generatorName;
//	}
//	public void setGeneratorName(String generatorName) {
//		if("SEQUENCE".equals(generatorName)){
//			this.generator = new SequenceGenerator(this);
//		}else if("GUID".equals(generatorName)){
//			this.generator = new GUIDGenerator(this);
//		}else if("UUID".equals(generatorName)){
//			this.generator = new UUIDGenerator();
//		}else{
//			throw new RuntimeException("IdGenerator with name " + generatorName + "is  not implemented yet,current accept names are SEQUENCE,UUID,GUID ...");
//		}
//		this.generatorName = generatorName;
//	}
	/**
	 * 默认构造函数
	 *
	 */
	public PersisterDao(){}
	/**
	 * 
	 * @param ds
	 */
	public PersisterDao(DataSource ds){
		setDatasource(ds);
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getPersister()
	 */
	public Persister getPersister(){
		return persister;
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#setPersister(bingo.persister.Persister)
	 */
	public void setPersister(Persister persister) {
		this.persister = persister;
	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getPersister(java.lang.String)
	 */
	public Persister getPersister(String datasourceKey){
		synchronized (persisterHolder) {
			if(!persisterHolder.containsKey(datasourceKey)){
				persisterHolder.put(datasourceKey, new Persister(getDataSource(datasourceKey)));
			}
		}
		return persisterHolder.get(datasourceKey);
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.temp#getBaseDao(java.lang.String)
	 */
	public BaseDao getBaseDao(String datasourceKey) {
		synchronized (daoHolder) {
			if(!daoHolder.containsKey(datasourceKey)){
				daoHolder.put(datasourceKey, new PersisterDao(getDataSource(datasourceKey)));
			}
		}
		return daoHolder.get(datasourceKey);
	}



	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#save(java.lang.Object)
	 */
	public int save(Object obj) {
		if(obj == null){
			throw new IllegalArgumentException("object to save can not be null ... ");
		}
		if(obj instanceof String ||ClassUtil.isPrimitiveOrWrapper(obj.getClass())){
			throw new IllegalArgumentException("no primitive or String type object is acceptted ... ");
		}
		log.debug("saving " + obj.getClass().getSimpleName() + " instance ...");
		int res = persister.insert(obj);
		log.debug("save " + obj.getClass().getSimpleName() + " successful ...");
		return res;

	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#update(java.lang.Object)
	 */
	public int update(Object obj) {
		if(obj == null) {
			throw new IllegalArgumentException("object to update can not be null ...");
		}
		if(obj instanceof String ||ClassUtil.isPrimitiveOrWrapper(obj.getClass())){
			throw new IllegalArgumentException("no primitive or String type object is acceptted ... ");
		}
		log.debug("updating " + obj.getClass().getSimpleName() + " instance ...");
		int result = persister.update(obj);
		log.debug("update " + obj.getClass().getSimpleName() + " successful ...");
		return result;
	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#delete(java.lang.Object)
	 */
	public int delete(Object obj) {
		if(obj == null){
			throw new IllegalArgumentException("object to delete can not be null ...");
		}
		if(obj instanceof String ||ClassUtil.isPrimitiveOrWrapper(obj.getClass())){
			throw new IllegalArgumentException("no primitive or String type object is acceptted ... ");
		}
		Class<?> objType = obj.getClass();
		log.debug("deleting " + objType.getSimpleName() +" instance ...");
		int result = persister.delete(obj);
		log.debug("delete " + objType.getSimpleName() +" successful ...");
		return result;
	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#delete(java.lang.Class, java.io.Serializable)
	 */
	public <T> int delete(Class <T> objType,Object id) {
		if(id == null){
			throw new IllegalArgumentException("id of the object to delete can not be null ...");
		}
		if(objType == String.class ||ClassUtil.isPrimitiveOrWrapper(objType)){
			throw new IllegalArgumentException("no primitive or String type object is acceptted ... ");
		}
		log.debug("deleting " + objType.getSimpleName() +" instance by id ：" + id);
		int result = persister.delete(objType, id);
		log.debug("delete " + objType.getSimpleName() +" successful ...");
		return result;
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#get(java.lang.Class, java.io.Serializable)
	 */
	@SuppressWarnings("unchecked")
	public <T> T load(Class<T> clazz,Object id){
		if(id == null){
			throw new IllegalArgumentException("id of the object to load can not be null ...");
		}
		log.debug("querying " + clazz.getSimpleName() +" instance by id ：" + id);
		T result = null;
		result = (T) persister.readByPK(clazz, id);
		if(result == null){
			log.debug("no object with id :[" + id +"] exists ...");
		} else {
			log.debug("query object successful ...");
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#get(java.lang.Class, java.lang.String, java.lang.Object)
	 */
	@SuppressWarnings("unchecked")
	public <T> T get(final Class<T> returnType,String customSQL,final Object... params){
		String querySQL = SQLProvider.getSQL(customSQL);
		T result = null;
		if(returnType == String.class || ClassUtil.isPrimitiveOrWrapper(returnType)){
			result = persister.readScalar(returnType, querySQL, params);
		}else if(SQLHelper.isSelectFromSingleTable(querySQL)){//单表查询
			try{
				result = (T) persister.read(returnType, querySQL, params);
			}catch (Exception e) {
			    try{
			    	Map resultMap = persister.readMap(querySQL, params);
			    	result = returnType.newInstance();
			    	BeanHelper.Map2ObjectIgnoreCase(resultMap, result);
			    }catch (Exception ex) {
			    	throw new RuntimeException("persister 单表查询时出现错误 ...",ex);
				}
			}
		}else{//非单表查询
			try{
		    	Map resultMap = persister.readMap(querySQL, params);
		    	result = returnType.newInstance();
		    	BeanHelper.Map2ObjectIgnoreCase(resultMap, result);
		    }catch (Exception ex) {
		    	throw new RuntimeException("persister 多表查询时出现错误 ...",ex);
			}
		}
		if(result == null){
			log.debug("no object found ...");
		} else {
			log.debug("query object successful ...");
		}
		return result;
	} 




	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#list(java.lang.Class, java.lang.String, java.lang.Object)
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> list(final Class<T> returnType,final String customSQL,final Object... params){
		List<T> result = null;
		//获取查询的sql
		String querySQL = SQLProvider.getSQL(customSQL);
		//处理字符串类型或者基本数据类型
		if(returnType == String.class || ClassUtil.isPrimitiveOrWrapper(returnType)){
			try {
				result = (List<T>)persister.getJdbcDao().read(querySQL, params, new ResultSetProcessorBase(){
					@Override
					public void init(ResultSet result) throws SQLException, PersistenceException {
						this.result = new ArrayList<T>();
					}
					@Override
					public void process(ResultSet result, IDaos daos) throws SQLException, PersistenceException {
						Method m;
						Object typedResult = null;
						if(returnType == Character.class ||returnType == char.class){
							typedResult = result.getString(1).length() > 0 ?result.getString(1).charAt(0) : ' ';
						}else if(returnType == Integer.class ||returnType == int.class ){
							typedResult = result.getInt(1);
						}else{
							try {
								String typeName = returnType.isPrimitive() ? ClassUtil.getWapperClassShortName(returnType):returnType.getSimpleName();
								m = ResultSet.class.getMethod("get"+ typeName, int.class);
								typedResult = m.invoke(result, 1);
							} catch (Exception e) {
								log.error(e.getCause().getMessage());
							}
						}
						((List)this.result).add(typedResult);
					}

				});
			} catch (PersistenceException e) {
				throw new RuntimeException("通过sql查询数据时发生异常");
			}
		}else{
			//处理用户自定义类型
			try{
				result = persister.query(returnType, querySQL, params);
			}catch (Exception e) {//没有和类对应的表
				List<Map<String,Object>> tempResult = persister.readMapList(querySQL, params);
				if(tempResult !=null && !tempResult.isEmpty()){
					result = new ArrayList<T>(tempResult.size());
					for(Map <String,Object> map : tempResult){
						try {
							T targetBean = returnType.newInstance();
							BeanHelper.Map2ObjectIgnoreCase(map, targetBean);
							result.add(targetBean);
						} catch (Exception ex) {
							log.error(ex);
						}						
					}
				}
			}
		}
		if(result == null || result.isEmpty()){
			log.debug("no object found ...");
			result = Collections.emptyList();
		} else {
			log.debug("query object successful ...");
		}
		return result;
	} 

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#update(java.lang.String, java.lang.Object)
	 */
	@SuppressWarnings("unchecked")
	public int update(final String customSQL,final Object...params){
		String sql =SQLProvider.getSQL(customSQL);
		log.debug("execute update by sql : " + sql);
		Integer count= persister.execute(sql, params);
		log.debug("update successful ...");
		return count;
	}



	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#list(java.lang.String, java.lang.Object)
	 */
	public List<Map<String,Object>> list(final String customSQL,final Object... params){
		String sql =SQLProvider.getSQL(customSQL);
		log.debug("read map list by sql : " + sql);
		List<Map<String,Object>> result = persister.readMapList(sql, params);
		if(result == null){
			result = Collections.emptyList();
		}
		log.debug("read successful ...");
		return result;

	}

	public <T> List<T> listByDynamicSQL(final Class<T> returnType,final String customSQL,final Object... args) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		DynamicSQL sql =SQLProvider.getDynamicSQL(customSQL, args);
		return list(returnType, sql.getSql(), sql.getParams());

	}
	
	public List<Map<String,Object>> listByDynamicSQL(final String customSQL,final Object paramBean) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		DynamicSQL sql =SQLProvider.getDynamicSQL(customSQL, paramBean);
		log.debug("read map list by sql : " + sql);
		List<Map<String,Object>> result = persister.readMapList(sql.getSql(), sql.getParams());
		if(result == null){
			result = Collections.emptyList();
		}
		log.debug("read successful ...");
		return result;

	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#pageQueryByDynamicSQL(java.lang.String, java.lang.Object, org.ecside.table.limit.Limit)
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> pageQueryByDynamicSQL(String sqlKey,Object paramBean,Limit limit) throws Exception{
		return pageQueryByDynamicSQL(sqlKey, paramBean, limit, false);
	}
	
	public List<Map<String,Object>> pageQueryByDynamicSQL(String querySQLKey,Object paramBean,String countSQLKey,Object countParamBean,Limit limit) throws Exception{
		return pageQueryByDynamicSQL(querySQLKey, paramBean, countSQLKey, countParamBean, limit, null);
	}
	
	public List<Map<String,Object>> pageQueryByDynamicSQL(String querySQLKey,Object paramBean,String countSQLKey,Object countParamBean,Limit limit,Integer totalRowCount) throws Exception{
		int pageSize = limit.getCurrentRowsDisplayed();
		if(paramBean instanceof BaseActionForm || paramBean instanceof BaseLazyDynaForm){
			if(paramBean instanceof BaseActionForm){
				((BaseActionForm)paramBean).setLimit(limit);
				pageSize = ((BaseActionForm)paramBean).getPageSize();
			}else{
				((BaseLazyDynaForm)paramBean).setLimit(limit);
				pageSize = ((BaseLazyDynaForm)paramBean).getPageSize();
			}
		}
		Map filterMap = LimitUtil.getFilterMap(limit);
		if(!filterMap.isEmpty()){
			if(paramBean instanceof Map){
				((Map)paramBean).putAll(filterMap);
			}else{
				BeanHelper.Map2ObjectIgnoreCase(filterMap, paramBean);
			}
		}
		if(paramBean instanceof Map){
			((Map)paramBean).put("orderProperty",LimitUtil.getOrderProperty(limit));
		}
		DynamicSQL dynamicSQL = SQLProvider.getDynamicSQL(querySQLKey, paramBean);
		String sourceSQL = dynamicSQL.getSql();
		Object[] srcParams = dynamicSQL.getParams();
		Persister p = getPersister();
		//如果pageSize小于0,则认为是不分页查询
		if(pageSize < 0){
			//一次性查询,不进行分页查询
			return p.readMapList(sourceSQL,srcParams);
		}else{
			//查询总记录数
			int rowCount = 0;
			if (totalRowCount == null) {
				DynamicSQL countDynamicSQL = SQLProvider.getDynamicSQL(querySQLKey, paramBean);
				String countQuerySQL = countDynamicSQL.getSql();
				Object[] countParams = countDynamicSQL.getParams();
				rowCount = p.readScalar(Integer.class, countQuerySQL,countParams);
			}else{
				rowCount = totalRowCount;
			}
			//可以通过form设置每页显示记录数目
			limit.setRowAttributes(rowCount, pageSize);
			if(rowCount == 0){
				return Collections.EMPTY_LIST;
			}
			//获取分页查询的sql
			String pageQuerySQL = SQLProvider.getPageSQL(sourceSQL);
			//扩展关于分页的参数
			int srcParamLength = srcParams.length;
			Object []params = new Object[srcParamLength + 2];
			System.arraycopy(srcParams, 0, params, 0, srcParamLength);
			//结束行
			params[srcParamLength] = limit.getRowEnd();
			//开始行
			params[srcParamLength +1] = limit.getRowStart();
			//查询
			return p.readMapList(pageQuerySQL,params);
		}
		

	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#pageQueryBySQL(net.bingosoft.common.data.Pager, java.lang.String, java.lang.Object)
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> pageQueryBySQL(Pager pager,String sqlKey,Object... sqlParams) throws Exception{
		String sourceSQL = SQLProvider.getSQL(sqlKey);
		//获取查询总数的sql
		String countSQL = SQLProvider.getCountSQL(sourceSQL);
		Persister p = getPersister();
		//查询总记录数
		int rowCount = Integer.valueOf(p.readString(countSQL,sqlParams));
		pager.setTotalRows(rowCount);
		if(rowCount == 0){
			return Collections.EMPTY_LIST;
		}
		//获取分页查询的sql
		String pageQuerySQL = SQLProvider.getPageSQL(sourceSQL);
		//扩展关于分页的参数		
		int srcParamLength = sqlParams.length;
		Object []params = new Object[srcParamLength + 2];
		System.arraycopy(sqlParams, 0, params, 0, srcParamLength);
		//(rowStart,rowEnd]
		//结束行
		params[srcParamLength] = pager.getRowEnd();
		//开始行
		params[srcParamLength +1] = pager.getRowStart();
		//查询	
		return p.readMapList(pageQuerySQL,params);

	}



	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#pageQueryBySQL(net.bingosoft.common.data.Pager, java.lang.Class, java.lang.String, java.lang.Object)
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> pageQueryBySQL(Pager pager,Class<T> returnType,String sqlKey,Object... sqlParams) throws Exception{
		String sourceSQL = SQLProvider.getSQL(sqlKey);
		//获取查询总数的sql
		String countSQL = SQLProvider.getCountSQL(sourceSQL);
		Persister p = getPersister();
		//查询总记录数
		int rowCount = Integer.valueOf(p.readString(countSQL,sqlParams));
		pager.setTotalRows(rowCount);
		if(rowCount == 0){
			return Collections.EMPTY_LIST;
		}
		//获取分页查询的sql
		String pageQuerySQL = SQLProvider.getPageSQL(sourceSQL);
		//扩展关于分页的参数		
		int srcParamLength = sqlParams.length;
		Object []params = new Object[srcParamLength + 2];
		System.arraycopy(sqlParams, 0, params, 0, srcParamLength);
		//(rowStart,rowEnd]
		//结束行
		params[srcParamLength] = pager.getRowEnd();
		//开始行
		params[srcParamLength +1] = pager.getRowStart();
		//查询	
		return p.query(returnType, pageQuerySQL, params);

	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getCodeMap(java.lang.String, java.lang.Object)
	 */
	public Map<String, String> getCodeMap(String sqlId ,Object... params){
		String sqlOrKey = SQLProvider.getSQL(sqlId);
		List<Map<String,Object>> typeMap = getPersister().readMapList(sqlOrKey,params);
		Map<String, String> keyValueMap = new LinkedHashMap<String, String>(typeMap.size());
		for(Map<String,Object> map: typeMap ){
			Object key = map.get("VALUE");
			if(key == null){
				key = map.get("KEY");
			}
			Object name = map.get("NAME");
			if(name == null){
				name = map.get("LABEL");
			}
			if(key != null && name != null){
				keyValueMap.put(key.toString(),name.toString());
			}
		}
		return keyValueMap;
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#setBeanFactory(org.springframework.beans.factory.BeanFactory)
	 */
	public void setBeanFactory(BeanFactory beanFacotry) throws BeansException {
		this.factory = beanFacotry;
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getDataSource(java.lang.String)
	 */
	public DataSource getDataSource(String dsKey){
		if(this.factory.containsBean(dsKey)){
			return (DataSource)this.factory.getBean(dsKey);
		}else{
			log.error("指定数据源["+dsKey+"]不存在,请检查...");
			return null; 
		}
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getSequence()
	 */
	public Long getSequence(){
		return BeanHelper.getLong(getPersister().readString("select "+ getSequenceName() + ".Nextval from dual",new Object[]{}));
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getSequence(java.lang.String)
	 */
	public Long getSequence(String sequenceName){
		return BeanHelper.getLong(getPersister().readString("select "+ sequenceName + ".Nextval from dual",new Object[]{}));
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#executeProcedure(java.lang.String, java.lang.Object)
	 */
	public void executeProcedure(String procedureName,Object[] inputParams) throws SQLException{
		//prepare ProcecedureSQL
		String callProcecedureSQL = null;
		if(inputParams == null || inputParams.length == 0){
			callProcecedureSQL = "{call " + procedureName + "()}";
		}else{
			callProcecedureSQL = "{call " + procedureName + "(";
			for(int i=0;i<inputParams.length;i++){
				callProcecedureSQL += "?,";
			}           
			callProcecedureSQL = callProcecedureSQL.substring(0,callProcecedureSQL.length() - 1);
			callProcecedureSQL += ") }";
		}
		log.debug("ProcecedureSQL: " + callProcecedureSQL);
		//excute ProcecedureSQL
		CallableStatement statement = null;
		try {
			statement = persister.getDaos().getConnection().prepareCall(callProcecedureSQL);
			if(inputParams != null && inputParams.length > 0){
				for(int i=1;i<=inputParams.length;i++){
					statement.setObject(i,inputParams[i-1]);
				}
			}
			statement.execute();
		} finally{
			JdbcUtils.closeStatement(statement);
			persister.getDaos().resetConnection();
		}
	}
	
	public void executeProcedure(String procedureName) throws SQLException{
		executeProcedure(procedureName,null);
	}
	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#executeProcedure(java.lang.String, java.lang.Object)
	 */
	public Object executeProcedure(String procedureName,ICallableStatementProcessor processor,Object... inputParams) throws SQLException{
		//prepare ProcecedureSQL
		String callProcecedureSQL = null;
		if(inputParams.length == 0){
			callProcecedureSQL = "{call " + procedureName + "()}";
		}else{
			callProcecedureSQL = "{ call " + procedureName + "(";
			for(int i=0;i<inputParams.length;i++){
				callProcecedureSQL += "?,";
			}           
			callProcecedureSQL = callProcecedureSQL.substring(0,callProcecedureSQL.length() -1);
			callProcecedureSQL += ") }";
		}
		log.debug("ProcecedureSQL: " + callProcecedureSQL);
		//excute ProcecedureSQL
		CallableStatement statement = null;
		try {
			statement = persister.getDaos().getConnection().prepareCall(callProcecedureSQL);
			processor.beforeExecute(statement,inputParams);			
			statement.execute();
			return processor.afterExecute(statement);
		} finally{
			JdbcUtils.closeStatement(statement);
			persister.getDaos().resetConnection();
		}
	}	
	
	public Object[] executeProcedure(String procedureName,Object[] inputParams,int ...outParamTypes) throws SQLException{
		//prepare ProcecedureSQL
		String callProcecedureSQL = null;
		int paramLength = inputParams.length + outParamTypes.length;
		if(paramLength == 0){
			callProcecedureSQL = "{call " + procedureName + "()}";
		}else{
			callProcecedureSQL = "{ call " + procedureName + "(";
			for(int i = 0;i< paramLength ; i++){
				callProcecedureSQL += "?,";
			}           
			callProcecedureSQL = callProcecedureSQL.substring(0,callProcecedureSQL.length() -1);
			callProcecedureSQL += ") }";
		}
		log.debug("ProcecedureSQL: " + callProcecedureSQL);
		//excute ProcecedureSQL
		CallableStatement statement = null;
		try {
			statement = persister.getDaos().getConnection().prepareCall(callProcecedureSQL);
			//设定输入参数
			for(int i=1;i<=inputParams.length;i++){
				statement.setObject(i,inputParams[i-1]);
			}
			//注册输出参数
			for (int i = 0; i< outParamTypes.length; i++){
				statement.registerOutParameter(inputParams.length + i +1, outParamTypes[i]);
			}
			statement.execute();
			Object[] executeResult = new Object[outParamTypes.length];
			//获取返回结果
			for (int i = 0; i< outParamTypes.length; i++){
				executeResult[i] = statement.getObject(inputParams.length + i +1);
			}
			return executeResult;
		} finally{
			JdbcUtils.closeStatement(statement);
			persister.getDaos().resetConnection();
		}
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#executeProcedure(java.lang.String, java.lang.String, java.lang.Object)
	 */
	public void executeProcedureOnDatasource(String dataSourceKey,String procedureName,Object... inputParams) throws SQLException{
		//prepare ProcecedureSQL
		String callProcecedureSQL = null;
		if(inputParams.length == 0){
			callProcecedureSQL = "{call " + procedureName + "()}";
		}else{
			callProcecedureSQL = "{ call " + procedureName + "(";
			for(int i=0;i<inputParams.length;i++){
				callProcecedureSQL += "?,";
			}           
			callProcecedureSQL = callProcecedureSQL.substring(0,callProcecedureSQL.length() -1);
			callProcecedureSQL += ") }";
		}
		log.debug("ProcecedureSQL: " + callProcecedureSQL);
		//excute ProcecedureSQL
		CallableStatement statement = null;
		Connection con = null;
		try {
			con = getDataSource(dataSourceKey).getConnection();
			statement = con.prepareCall(callProcecedureSQL);
			for(int i=1;i<=inputParams.length;i++){
				statement.setObject(i,inputParams[i-1]);
			}
			statement.execute();
		} finally{
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(con);
		}
	}


	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#getDatasource()
	 */
	public DataSource getDatasource() {
		return datasource;
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#setDatasource(javax.sql.DataSource)
	 */
	public void setDatasource(DataSource datasource) {
		this.datasource = datasource;
		if(this.persister == null){
			this.persister = new Persister(datasource);
		}
	}

	/* (non-Javadoc)
	 * @see net.bingosoft.common.data.BaseDao#readScalar(java.lang.Class, java.lang.String, java.lang.Object)
	 */
	public <T> T readScalar(Class<? extends T> type, String customSQL, Object... params) {
		customSQL = SQLProvider.getSQL(customSQL);
		if(ClassUtil.isPrimitiveOrWrapper(type) || type == String.class){
			return persister.readScalar(type, customSQL, params);
		}else{
			return persister.read(type, customSQL, params);
		}
	}

	/**
	 * 检查指定类型的对象是否存在
	 */
	@SuppressWarnings("unchecked")
	public boolean exists(Class objType, Object id) {
		return persister.readByPK(objType,id) != null;
	}
	/**
	 * 通过sql检查指定对象是否存在
	 */
	public boolean exists(String sqlOrKey, Object... params) {
		return persister.exists(SQLProvider.getSQL(sqlOrKey), params);
	}

	public ValueBean get(String sqlOrKey, Object... params) {
		return new ValueBean(persister.readMap(SQLProvider.getSQL(sqlOrKey), params));
	}

	public List<ValueBean> listValueBeans(String customSQL, Object... params) {
		List<Map<String,Object>> result = persister.readMapList(SQLProvider.getSQL(customSQL), params);
		if(result != null && !result.isEmpty()){
			List<ValueBean> resultList = new ArrayList<ValueBean>(result.size());
			for(Map<String,Object> bean : result){
				resultList.add(new ValueBean(bean));
			}
			return resultList;
		}else{
			return Collections.emptyList();
		}
	}

	public Map<String, Object> readMap(String sqlOrKey, Object... params) {
		return persister.readMap(SQLProvider.getSQL(sqlOrKey),params);
	}

	public String readString(String sqlOrKey, Object... params) {
		return persister.readString(SQLProvider.getSQL(sqlOrKey), params);
	}
	/**
	 * 通过动态sql进行查询
	 */
	public List<Map<String, Object>> queryByDynamaicSQL(String sqlOrKey, Object paramBean) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		DynamicSQL sql = SQLProvider.getDynamicSQL(sqlOrKey, paramBean);
		List <Map<String, Object>>result= persister.readMapList(sql.getSql(),sql.getParams());
		if(result == null){
			result = Collections.emptyList();
		}
		return result;
	}
	
	public List<Map<String,Object>> findByNamedQuery(String queryName, Object... values) {
		return list(SQLProvider.getSQL(queryName), values);
	}
	
	public int delete(String sqlOrKey, Object... params) {
		return persister.execute(SQLProvider.getSQL(sqlOrKey), params);
	}

	public JSONObject getJSON(String sqlOrKey, Object... params) {
		Map result = readMap(sqlOrKey, params);
		try {
			return (JSONObject) JSONConverter.toJSON(result,null);
		} catch (Exception e) {
			throw new RuntimeException("通过sql语句获取json对象失败...",e);
		}
	}

	public JSONArray getJSONArray(String sqlOrKey, Object... params) {
		List<Map<String,Object>> result = list(sqlOrKey, params);
		JSONArray array = new JSONArray();
		if(!result.isEmpty()){
			for(Map <String,Object> bean : result){
				try {
					array.put(JSONConverter.toJSON(bean,null));
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return array;
	}
    /**
     * 开启事务
     */
	public void begin() {
		persister.getDaos().beginTransaction();
		log.debug("Persister 事务开启...");
	}
    /**
     * 提交事务
     */
	public void commit() {
		persister.getDaos().commit();
		log.debug("Persister 事务提交...");
	}
   
    /**
     *	回滚事务
     */
	public void rollback() {
		persister.getDaos().rollback();
		log.debug("Persister 事务回滚...");
	}
	/**
	 * 获取一个GUID字符串
	 */
	public String getGUID() {
		return persister.readString("select sys_guid() from dual",new Object[]{});
	}

	public int save(String tableName, Map<String, Object> columnNames) {
		StringBuffer sb = new StringBuffer(150);
		sb.append("INSERT INTO ").append(tableName.toLowerCase()).append("(");
		StringBuffer valueBuffer = new StringBuffer(150);
		List<Object> paramsList = new ArrayList<Object>();
		boolean isFirstField = true;
		for(String field : columnNames.keySet()){
			Object value = columnNames.get(field);
			if(value != null && value != JSONObject.NULL){
				if(isFirstField){
					sb.append(field);
					valueBuffer.append(" VALUES(").append("?");
				}else{
					sb.append(",").append(field);
					valueBuffer.append(",?");
				}
				paramsList.add(value);
				isFirstField = false;
			}
		}
		sb.append(")").append(valueBuffer).append(")");
		String finalSQL = sb.toString();
		log.debug("生成的插入sql为 : " +finalSQL);
		return persister.execute(finalSQL, paramsList.toArray());
	}
	
	
	public int updateByMap(String tableName,Map<String, Object> columnNames,Map<String,Object> keyMap){
		StringBuffer sb = new StringBuffer(150);
		sb.append("UPDATE ").append(tableName.toUpperCase()).append(" T ");
		List<Object> paramsList = new ArrayList<Object>();
		boolean isFirstField = true;
		for(String field : columnNames.keySet()){
			Object value = columnNames.get(field);
			if(value != null && value != JSONObject.NULL){
				if(isFirstField){
					sb.append("SET T.").append(field.toUpperCase()).append(" = ? ");
				}else{
					sb.append(",").append(" T.").append(field.toUpperCase()).append(" = ? ");
				}
				paramsList.add(value);
				isFirstField = false;
			}
			
		}
		boolean isFirstKeyField = true;
		for(String keyField : keyMap.keySet()){
			Object value = keyMap.get(keyField);
			if(value != null && value != JSONObject.NULL){
				if(isFirstKeyField){
					sb.append(" WHERE T.").append(keyField).append(" = ? ");
				}else{
					sb.append(" AND T.").append(keyField).append(" = ? ");
				}
				paramsList.add(value);
				isFirstKeyField = false;
			}
		}
		String updateSQL = sb.toString();
        log.debug("更新的sql语句: " +updateSQL);
        return persister.execute(updateSQL, paramsList.toArray());
		
	}
	/**
	 * 通过表名称和主键和主键值的映射实现删除
	 * @param tableName 表的名称
	 * @param keyMap 主键名称到主键值的映射,支持多主键
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public int deleteByTableNameAndKeyMap(String tableName, Map<String, Object> keyMap) {
		List paramsList = new ArrayList();
		StringBuffer sb = new StringBuffer("DELETE FROM ");
		sb.append(tableName).append(" T ");		
		boolean isFirstKeyField = true;
		for(String keyField : keyMap.keySet()){
			Object value = keyMap.get(keyField);
			if(value != null && value != JSONObject.NULL){
				if(isFirstKeyField){
					sb.append(" WHERE T.").append(keyField).append(" = ? ");
				}else{
					sb.append(" AND T.").append(keyField).append(" = ? ");
				}
				paramsList.add(value);
				isFirstKeyField = false;
			}
		}
		String deleteSQL = sb.toString();
        log.debug("删除的sql语句: " +deleteSQL);
        return persister.execute(deleteSQL, paramsList.toArray());
	}
	/**
	 * 通过SQL语句进行分页查询
	 * @param sqlKey sql语句或sql语句在sql.xml中的键值
	 * @param sqlParams sql查询语句的参数
	 * @param limit 分页查询对象
	 * @param showAllRecords 是否显示所有记录
	 * @return List<Map<String,Object>> 一页记录结果
	 * @throws Exception
	 */
	public List<Map<String,Object>> pageQueryBySQL(String sqlKey,Object[] sqlParams,Limit limit,boolean showAllRecords) throws Exception{
		String sourceSQL = SQLProvider.getSQL(sqlKey);
		//获取查询总数的sql
		String countSQL = SQLProvider.getCountSQL(sourceSQL);
		Persister p = getPersister();
		//查询总记录数
		int rowCount = Integer.valueOf(p.readString(countSQL,sqlParams));
		limit.setRowAttributes(rowCount, showAllRecords ? -1 : 0);
		if(rowCount == 0){
			return Collections.emptyList();
		}
		//获取分页查询的sql
		String pageQuerySQL = SQLProvider.getPageSQL(sourceSQL);
		//扩展关于分页的参数		
		int srcParamLength = sqlParams.length;
		Object []params = new Object[srcParamLength + 2];
		System.arraycopy(sqlParams, 0, params, 0, srcParamLength);
		//结束行
		params[srcParamLength] = limit.getRowEnd();
		//开始行
		params[srcParamLength +1] = limit.getRowStart();
		//查询
		return p.readMapList(pageQuerySQL,params);
		
	}
	
//	public Object saveOrUpdate(IdAware object) {
//		if(object.isIdExists()){
//			log.debug("update object ...");
//			persister.update(object);
//		}else{
//			if(this.generator == null){
//				throw new RuntimeException("IdGenerator is not set yet,please set IdGenerator by name ...");
//			}
//			log.debug("insert object ...");
//			object.setIdentifier(this.generator.generateId());
//			persister.insert(object);
//		}
//		return object.getIdentifier();
//		
//	}
	
	public String getSequenceName() {
		return sequenceName == null ? defalutSequenceName : sequenceName;
	}
	public void setSequenceName(String sequenceName) {
		this.sequenceName = sequenceName;
	}
	public List<Map<String, Object>> pageQueryByDynamicSQL(String sqlKey,
			Object paramBean, Limit limit, boolean optimize) throws Exception {
		int pageSize = limit.getCurrentRowsDisplayed();
		if(paramBean instanceof BaseActionForm || paramBean instanceof BaseLazyDynaForm){
			if(paramBean instanceof BaseActionForm){
				((BaseActionForm)paramBean).setLimit(limit);
				pageSize = ((BaseActionForm)paramBean).getPageSize();
			}else{
				((BaseLazyDynaForm)paramBean).setLimit(limit);
				pageSize = ((BaseLazyDynaForm)paramBean).getPageSize();
			}
		}
		Map filterMap = LimitUtil.getFilterMap(limit);
		if(!filterMap.isEmpty()){
			if(paramBean instanceof Map){
				((Map)paramBean).putAll(filterMap);
			}else{
				BeanHelper.Map2ObjectIgnoreCase(filterMap, paramBean);
			}
		}
		if(paramBean instanceof Map){
			((Map)paramBean).put("orderProperty",LimitUtil.getOrderProperty(limit));
		}
		DynamicSQL dynamicSQL = SQLProvider.getDynamicSQL(sqlKey, paramBean);
		String sourceSQL = dynamicSQL.getSql();
		Object[] srcParams = dynamicSQL.getParams();
		Persister p = getPersister();
		//如果pageSize小于0,则认为是不分页查询
		if(pageSize < 0){
			//一次性查询,不进行分页查询
			return p.readMapList(sourceSQL,srcParams);
		}else{
			//获取查询总数的sql
			String countSQL = SQLProvider.getCountSQL(sourceSQL,optimize);
			//查询总记录数
			int rowCount = p.readScalar(Integer.class,countSQL,srcParams);
			//可以通过form设置每页显示记录数目
			limit.setRowAttributes(rowCount, pageSize);
			if(rowCount == 0){
				return Collections.EMPTY_LIST;
			}
			//获取分页查询的sql
			String pageQuerySQL = SQLProvider.getPageSQL(sourceSQL);
			//扩展关于分页的参数
			int srcParamLength = srcParams.length;
			Object []params = new Object[srcParamLength + 2];
			System.arraycopy(srcParams, 0, params, 0, srcParamLength);
			//结束行
			params[srcParamLength] = limit.getRowEnd();
			//开始行
			params[srcParamLength +1] = limit.getRowStart();
			//查询
			return p.readMapList(pageQuerySQL,params);
		}
	}
	
	
	public int deleteByTableNameAndPrimaryKey(String tableName,String primaryKeyColumnName, Object primaryKeyValue) {
		Map<String, Object> primaryKeyMap = new HashMap<String, Object>(1);
		primaryKeyMap.put(primaryKeyColumnName, primaryKeyValue);
		return deleteByTableNameAndKeyMap(tableName, primaryKeyMap);
	}
	
	
	public int insert(String customSQL, Object... params) {
		return update(customSQL, params);
	}
	public int updateByMap(String tableName,String primaryKeyColumnName, Map<String, Object> columnNames) {
		Map<String, Object> primaryKeyMap = new HashMap<String, Object>(1);
		primaryKeyMap.put(primaryKeyColumnName, columnNames.get(primaryKeyColumnName));
		return updateByMap(tableName, columnNames, primaryKeyMap);
	}
	public Map<String, Object> readMapByTableNameAndPrimaryKey(
			String tableName, String primaryKeyColumnName,
			Object primaryKeyValue) {
		return persister.readMap("select * from " + tableName + " t where t." + primaryKeyColumnName + "=?", primaryKeyValue);
	}
	
	public Connection currentConnection() throws SQLException {
		Connection con = persister.getDaos().getConnection();
		return con;
	}
	
	public Connection newConnection() throws SQLException {
		Connection con = datasource.getConnection();
		return con;
	}
	
//	public int batchDeleteByTableNameAndKeyMap(String tableName, Collection<Map<String, Object>> keyMapCollection) {
//		return 0;
//	}
//	public void batchSave(String tableName, Collection<Map<String, Object>> columnName2ValueMapList) {
//		
//	}
//	public void batchUpdate(String tableName, Collection<Map<Map<String, Object>, Map<String, Object>>> keyMap2ValueMap) {
//		
//	}
}
